package com.zygh.questionnairesurvey.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.zygh.questionnairesurvey.config.RedisCache;
import com.zygh.questionnairesurvey.entity.Questionnaire;
import com.zygh.questionnairesurvey.entity.UserInfo;
import org.apache.ibatis.annotations.CacheNamespace;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
@CacheNamespace(implementation = RedisCache.class, eviction = RedisCache.class)
public interface QuestionnaireMapper extends BaseMapper<Questionnaire> {


    /**
     * 应填报人数
     *
     * @param id 部门id
     * @return
     */
    @Select("<script> " +
            "SELECT COUNT(*) toInformant FROM user_info  " +
            "<where> <if test = 'id != null'> dept_id =#{id} </if> " +
            "AND term_status != 3 AND is_superadmin = 0 " +
            "</where> </script>")
    int count1(Integer id);


    /**
     * 填报人数
     *
     * @param id 部门id
     * @return
     */
    @Select("<script> " +
            "SELECT count(*) informant  FROM questionnaire q LEFT JOIN user_info u ON q.warningsignal = u.user_name " +
            "<where> <if test = 'id != null'> q.dept_id =#{id} </if> AND u.term_status != 3 AND u.is_superadmin = 0 " +
            "</where> </script>")
    int count2(Integer id);


    /**
     * 未自查出问题人数
     *
     * @param id 部门id
     * @return
     */
    @Select("<script> " +
            "SELECT COUNT(*) as noInformant FROM questionnaire q LEFT JOIN user_info u ON q.warningsignal = u.user_name  " +
            "<where> ISNULL(lowest) || LENGTH(trim(lowest)) &lt; 1 " +
            "AND u.term_status != 3 AND u.is_superadmin = 0 " +
            "<if test = 'id != null'> and q.dept_id =#{id} </if></where></script>")
    int count4(Integer id);

    /**
     * 单位问题条数
     *
     * @param id 部门id
     * @return
     */
    @Select("<script> " +
            "SELECT count(*) number FROM problem_details d LEFT JOIN user_info u ON d.warningsignal = u.user_name " +
            "<where> <if test = 'id != null'> u.dept_id =#{id} </if> AND u.term_status != 3 AND u.is_superadmin = 0 " +
            "</where> </script>")
    int count5(Integer id);


    /**
     * 班子人数
     *
     * @param id 部门id
     * @return
     */
    @Select("<script> " +
            "SELECT COUNT(*) termNum FROM user_info " +
            "<where> <if test = 'id != null'>dept_id =#{id} </if> " +
            "and  term_status = 1 " +
            "</where> </script>")
    int count7(Integer id);

    /**
     * 班子问题条数
     *
     * @param id 部门id
     * @return
     */
    @Select("<script> " +
            "SELECT count(*) termProblemNum FROM problem_details d LEFT JOIN user_info u ON d.warningsignal = u.user_name " +
            "<where> <if test = 'id != null'> u.dept_id =#{id} </if> " +
            "AND term_status = 1 " +
            "</where> </script>")
    int count8(Integer id);


    /**
     * 没有激活人数
     *
     * @param id 部门id
     * @return Integer
     */
    @Select("<script> SELECT COUNT(*) " +
            "FROM questionnaire q RIGHT JOIN user_info u ON q.warningsignal  =  u.user_name   " +
            "<where>  q.warningsignal IS NULL <if test = 'id != null'> and u.dept_id =#{id} </if> " +
            " AND term_status != 3 AND is_superadmin = 0 " +
            " </where></script>")
    Integer count9(Integer id);


    /**
     * ==============================================================================================
     * 民警统计报表人员详情
     */


    /**
     * --------------------------------------------------------------------------------------------
     * 以下为工勤人员统计
     */

    /**
     * 应填报人数
     *
     * @param id 部门id
     * @return int
     */
    @Select("<script> " +
            "SELECT COUNT(*) toInformant FROM user_info  " +
            "<where> <if test = 'id != null'> dept_id =#{id} </if> " +
            "AND term_status = 3 AND is_superadmin = 0 " +
            "</where> </script>")
    int workcount1(Integer id);


    /**
     * 填报人数
     *
     * @param id 部门id
     * @return int
     */
    @Select("<script> " +
            "SELECT count(*) informant  FROM questionnaire q LEFT JOIN user_info u ON q.warningsignal = u.user_name " +
            "<where> <if test = 'id != null'> q.dept_id =#{id} </if> AND u.term_status = 3 AND u.is_superadmin = 0 " +
            "</where> </script>")
    int workcount2(Integer id);


    /**
     * 未自查出问题人数
     *
     * @param id 部门id
     * @return int
     */
    @Select("<script> " +
            "SELECT COUNT(*) as noInformant FROM questionnaire q LEFT JOIN user_info u ON q.warningsignal = u.user_name  " +
            "<where> ISNULL(lowest) || LENGTH(trim(lowest)) &lt; 1 " +
            " AND u.term_status = 3 AND u.is_superadmin = 0 " +
            "<if test = 'id != null'> and q.dept_id =#{id} </if></where></script>")
    int workcount4(Integer id);

    /**
     * 单位问题条数
     *
     * @param id 部门id
     * @return int
     */
    @Select("<script> " +
            "SELECT count(*) number FROM problem_details d LEFT JOIN user_info u ON d.warningsignal = u.user_name " +
            "<where> <if test = 'id != null'> u.dept_id =#{id} </if> AND u.term_status = 3 AND u.is_superadmin = 0 " +
            "</where> </script>")
    int workcount5(Integer id);


    /**
     * 未填报人数
     *
     * @param id 部门id
     * @return Integer
     */
    @Select("<script> SELECT COUNT(*) " +
            "FROM questionnaire q RIGHT JOIN user_info u ON q.warningsignal  =  u.user_name " +
            "<where>  q.warningsignal IS NULL <if test = 'id != null'> and u.dept_id =#{id} </if> " +
            " AND term_status = 3 AND is_superadmin = 0 " +
            " </where></script>")
    Integer workcount9(Integer id);

    //------------------------------------------------------------------- 统计

    /**
     * 通过大队和问题id查询涉及人员数量
     *
     * @param lowest 问题id
     * @param pid    大队id
     * @return 涉及人员数量
     */
    @Select("<script>" +
            "SELECT count(*) FROM questionnaire q\n" +
            "INNER JOIN dept d ON d.dept_id = q.dept_id \n" +
            "LEFT JOIN user_info ui ON q.warningsignal = ui.user_name " +
            "WHERE FIND_IN_SET(#{lowest},q.lowest) AND q.dept_id = #{pid}\n" +
            "<if test = 'null != termStatus and 3 == termStatus'>" +
            "AND ui.term_status = 3 " +
            "</if>" +
            "<if test = 'null != termStatus and 3 != termStatus'>" +
            "AND ui.term_status in (0,1) " +
            "</if>" +
            "ORDER BY q.dept_id ASC" +
            "</script>")
    Integer listPeopleOnDept(@Param("lowest") Integer lowest,
                             @Param("pid") Integer pid,
                             @Param("termStatus") Integer termStatus);

    /**
     * 通过父id是该大队查涉及人员数量
     *
     * @param lowest 问题id
     * @param pid    大队id
     * @return 涉及人员数量
     */
    @Select("<script>" +
            "SELECT count(*) FROM questionnaire q\n" +
            "INNER JOIN dept d ON d.dept_id = q.dept_id \n" +
            "LEFT JOIN user_info ui ON q.warningsignal = ui.user_name " +
            "WHERE FIND_IN_SET(#{lowest},q.lowest) AND d.pid = #{pid}\n" +
            "<if test = 'null != termStatus and 3 == termStatus'>" +
            "AND ui.term_status = 3 " +
            "</if>" +
            "<if test = 'null != termStatus and 3 != termStatus'>" +
            "AND ui.term_status in (0,1) " +
            "</if>" +
            "ORDER BY q.dept_id ASC" +
            "</script>")
    Integer listPeopleOnPid(@Param("lowest") Integer lowest,
                            @Param("pid") Integer pid,
                            @Param("termStatus") Integer termStatus);


    /**
     * 通过部门id和问题id查询人员
     *
     * @param deptId 部门id
     * @param lowest 问题id
     * @return 符合条件的人员信息
     */
    @Select("<script>" +
            "SELECT q.warningsignal AS user_name,q.name ,d2.name AS p_name,q.dept_name AS dept_name FROM questionnaire q " +
            "LEFT JOIN dept d ON d.dept_id = q.dept_id " +
            "LEFT JOIN dept d2 ON d2.dept_id = d.pid " +
            "LEFT JOIN user_info ui ON q.warningsignal = ui.user_name " +
            "WHERE find_in_set(#{lowest},q.lowest) " +
            "<if test='null != deptId'>" +
            "AND find_in_set(d.dept_id ,getChildrenOrgOfHr(#{deptId})) " +
            "</if>" +
            "<if test = 'null != termStatus and 3 == termStatus'>" +
            "AND ui.term_status = 3" +
            "</if>" +
            "<if test = 'null != termStatus and 3 != termStatus'>" +
            "AND ui.term_status in (0,1)" +
            "</if>" +
            "</script>")
    List<UserInfo> listPeople(@Param("deptId") Integer deptId,
                              @Param("lowest") Integer lowest,
                              @Param("termStatus") Integer termStatus);

    /**
     * 通过问题id查涉及人员总数
     *
     * @param lowest 问题id
     * @return 涉及人员数量
     */
    @Select("<script>" +
            "SELECT count(*) FROM questionnaire q\n" +
            "INNER JOIN dept d ON d.dept_id = q.dept_id " +
            "LEFT JOIN user_info ui ON ui.user_name = q.warningsignal \n" +
            "WHERE FIND_IN_SET(#{lowest},q.lowest) \n" +
            "<if test = 'null != termStatus and 3 == termStatus'>" +
            "AND ui.term_status = 3" +
            "</if>" +
            "<if test = 'null != termStatus and 3 != termStatus'>" +
            "AND ui.term_status in (0,1)" +
            "</if>" +
            "ORDER BY q.dept_id ASC" +
            "</script>")
    Integer listPeopleOnLowest(@Param("lowest") Integer lowest,
                               @Param("termStatus") Integer termStatus);


    /**
     * ======================================================================================================
     * 查询全部民警
     */

    /**
     * 应填报人数
     *
     * @param id 部门id
     * @return
     */
    @Select("<script> " +
            "SELECT COUNT(*) toInformant FROM user_info  " +
            "<where> <if test = 'id != null'> dept_id =#{id} </if> " +
            "AND is_superadmin = 0 " +
            "</where> </script>")
    int count1All(Integer id);


    /**
     * 填报人数
     *
     * @param id 部门id
     * @return
     */
    @Select("<script> " +
            "SELECT count(*) informant  FROM questionnaire q LEFT JOIN user_info u ON q.warningsignal = u.user_name " +
            "<where> <if test = 'id != null'> q.dept_id =#{id} </if>  AND u.is_superadmin = 0 " +
            "</where> </script>")
    int count2All(Integer id);


    /**
     * 未自查出问题人数
     *
     * @param id 部门id
     * @return
     */
    @Select("<script> " +
            "SELECT COUNT(*) as noInformant FROM questionnaire q LEFT JOIN user_info u ON q.warningsignal = u.user_name  " +
            "<where> ISNULL(lowest) || LENGTH(trim(lowest)) &lt; 1 " +
            " AND u.is_superadmin = 0 " +
            "<if test = 'id != null'> and q.dept_id =#{id} </if></where></script>")
    int count4All(Integer id);

    /**
     * 单位问题条数
     *
     * @param id 部门id
     * @return
     */
    @Select("<script> " +
            "SELECT count(*) number FROM problem_details d LEFT JOIN user_info u ON d.warningsignal = u.user_name " +
            "<where> <if test = 'id != null'> u.dept_id =#{id} </if>  AND u.is_superadmin = 0 " +
            "</where> </script>")
    int count5All(Integer id);


    /**
     * 班子人数
     *
     * @param id 部门id
     * @return
     */
    @Select("<script> " +
            "SELECT COUNT(*) termNum FROM user_info " +
            "<where> <if test = 'id != null'>dept_id =#{id} </if> " +
            "and  term_status = 1 " +
            "</where> </script>")
    int count7All(Integer id);

    /**
     * 班子问题条数
     *
     * @param id 部门id
     * @return
     */
    @Select("<script> " +
            "SELECT count(*) termProblemNum FROM problem_details d LEFT JOIN user_info u ON d.warningsignal = u.user_name " +
            "<where> <if test = 'id != null'> u.dept_id =#{id} </if> " +
            "AND term_status = 1 " +
            "</where> </script>")
    int count8All(Integer id);


    /**
     * 没有激活人数
     *
     * @param id 部门id
     * @return Integer
     */
    @Select("<script> SELECT COUNT(*) " +
            "FROM questionnaire q RIGHT JOIN user_info u ON q.warningsignal  =  u.user_name   " +
            "<where>  q.warningsignal IS NULL <if test = 'id != null'> and u.dept_id =#{id} </if> " +
            " AND is_superadmin = 0 " +
            " </where></script>")
    Integer count9All(Integer id);

    @Select("<script>" +
            "SELECT q.warningsignal FROM questionnaire q " +
            "LEFT JOIN user_info ui ON ui.user_name = q.warningsignal " +
            "WHERE 1 = 1 " +
            "<if test = 'null != listDeptId and listDeptId.size &gt; 0'>" +
            "AND q.dept_id IN " +
            "<foreach item='listDeptId' collection='listDeptId' index='index'  open='(' separator=',' close=')'>" +
            "#{listDeptId} " +
            "</foreach>" +
            "</if>" +
            "<if test = 'null != termStatus and 3 == termStatus'>" +
            "AND ui.term_status = 3" +
            "</if>" +
            "<if test = 'null != termStatus and 3 != termStatus'>" +
            "AND ui.term_status in (0,1)" +
            "</if>" +
            "</script>")
    List<String> listLowest(@Param("listDeptId") List<Integer> listDeptId,
                                   @Param("termStatus") Integer termStatus);



}